package com.cssw.fyzb.core.dao.team;

import com.cssw.fyzb.core.model.dodb.CmfAgent;
import com.cssw.fyzb.core.model.dodb.User;
import com.cssw.fyzb.core.model.dodb.UserTree;
import com.cssw.fyzb.core.model.dodb.UserTreeH;
import com.ydn.dbframe.plugin.activerecord.Db;
import com.ydn.dbframe.plugin.activerecord.Page;
import com.ydn.dbframe.plugin.activerecord.Record;
import com.ydn.dbframe.plugin.activerecord.parse.SqlParse;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Feng Chen
 */
@Repository
public class UserTreeDaoImpl implements UserTreeDao {

    @Override
    public List<User> getSuberBySupId(Long supId) {
        Map<String, Object> param = new HashMap<>();
        param.put("supId", supId);
        SqlParse sqlParse = new SqlParse(param);
        sqlParse.addSQL("SELECT u.* FROM t_user u, t_user_tree t");
        sqlParse.addSQL(" WHERE u.user_id = t.user_id");
        sqlParse.addSQL(" AND t.sup_user_id = :supId");
        return User.dao.find(sqlParse);
    }

    @Override
    public Page<User> getAllSuberBySupArr(Map param, int page, int limit) {
        SqlParse sqlParse = new SqlParse(param);
        sqlParse.addSQL("SELECT u.* from t_user_tree t right join t_user u on t.user_id=u.user_id");
        sqlParse.addSQL(" where t.sup_user_id = :userId");
        sqlParse.addSQL(" AND t.level <> 0");
        sqlParse.addSQL(" order by t.user_id");
        return User.dao.paginate(page, limit, sqlParse);
    }

    @Override
    public Page<User> getSuberBySupArr(Map param, int page, int limit) {
        SqlParse sqlParse = new SqlParse(param);
        sqlParse.addSQL("SELECT u.* from t_user_tree t right join t_user u on t.user_id=u.user_id");
        sqlParse.addSQL(" where t.sup_user_id = :userId");
        sqlParse.addSQL(" and u.face_auth = :faceAuth");
        sqlParse.addSQL(" AND t.level <> 0");
        sqlParse.addSQL(" order by t.user_id");
        return User.dao.paginate(page, limit, sqlParse);
    }

    @Override
    public Integer countSuberBySupId(Long supId) {
        String sql = "SELECT count(1) FROM t_user u, t_user_tree t " +
                "WHERE u.user_id = t.user_id " +
                "AND t.sup_user_id = ? ";
        return Db.queryInt(sql, supId);
    }

    @Override
    public Integer countTeamUserNumBySupId(Long supId) {
        String sql = "SELECT count(1) " +
                " FROM t_user_tree t" +
                " WHERE t.sup_user_id = ? " +
                " and t.level > 0 ";
        return Db.queryInt(sql, supId);
    }

    @Override
    public Integer countTeamAuthUserNumBySupId(Long supId) {
        String sql = "select count(1) from t_user_tree t1 " +
                "where t1.sup_user_id = ? and t1.level > 0 " +
                "and  exists ( select user_id from t_user t2 where t2.is_real = ? and t1.user_id = t2.user_id )";
        return Db.queryInt(sql, supId, User.IS_REAL_YES);
    }

    @Override
    public Integer countDirectSuberBySupId(Long supId) {
        String sql = "SELECT count(1) FROM t_user u, t_user_tree t " +
                "WHERE u.user_id = t.user_id " +
                "AND t.sup_user_id = ? " +
                "AND T.`level` = 1";
        return Db.queryInt(sql, supId);
    }

    @Override
    public Integer sumDirectSuberActiveBySupId(Long supId) {
        String sql = "SELECT SUM(e.active_num) " +
                "FROM t_user_ext e, t_user t " +
                "WHERE e.user_id = t.user_id " +
                "AND t.sup_user_id = ? ";
        return Db.queryInt(sql, supId);
    }

    @Override
    public Integer sumTeamUserActiveBySupId(Long supId) {
        String sql = "SELECT SUM(e.active_num) " +
                " FROM t_user_ext e, t_user_tree t " +
                " WHERE e.user_id = t.user_id " +
                " AND t.sup_user_id = ? " +
                " AND t.level > 0  ";
        return Db.queryInt(sql, supId);
    }

    @Override
    public Integer countVDirectSuberBySupId(Long supId) {
        String sql = "SELECT count(1) " +
                "FROM t_user t, t_user_ext e " +
                "WHERE t.user_id =  e.user_id " +
                "AND t.sup_user_id = ? " +
                "AND e.valid_user = 1";
        return Db.queryInt(sql, supId);
    }

    @Override
    public Integer countIDirectSuberBySupId(Long supId) {
        String sql = "SELECT count(1) " +
                "FROM t_user t, t_user_ext e " +
                "WHERE t.user_id =  e.user_id " +
                "AND t.sup_user_id = ? " +
                "AND e.valid_user = 0";
        return Db.queryInt(sql, supId);
    }

    @Override
    public List<User> getSuperByUserId(Long userId) {
        Map<String, Object> param = new HashMap<>();
        param.put("userId", userId);
        SqlParse sqlParse = new SqlParse(param);
        sqlParse.addSQL("SELECT u.* FROM t_user u, t_user_tree t");
        sqlParse.addSQL(" WHERE u.user_id = t.sup_user_id");
        sqlParse.addSQL(" AND t.user_id = :userId");
        return User.dao.find(sqlParse);
    }

    /**
     * 获取所有父元素
     *
     * @param userId
     * @return
     */
    public Page<UserTree> pageBySupUserId(Long userId, int pageNum, int pageSize){
        Map<String, Object> param = new HashMap<>();
        param.put("userId", userId);
        SqlParse sqlParse = new SqlParse(param);
        sqlParse.addSQL("SELECT * FROM t_user_tree ");
        sqlParse.addSQL(" WHERE sup_user_id=:userId ");
        sqlParse.addSQL(" order by id ");
        return UserTree.dao.paginate(pageNum, pageSize, sqlParse);
    }

    @Override
    public List<UserTree> getByUserId(Long userId) {
        Map<String, Object> param = new HashMap<>();
        param.put("userId", userId);
        SqlParse sqlParse = new SqlParse(param);
        sqlParse.addSQL("SELECT * FROM t_user_tree ");
        sqlParse.addSQL(" WHERE user_id=:userId ");
        sqlParse.addSQL(" order by level ");
        return UserTree.dao.find(sqlParse);
    }

    @Override
    public void save(Long userId, Long supId, Integer level, Long mktUserId) {
        UserTree userTree = new UserTree();
        userTree.setUserId(userId);
        userTree.setSupUserId(supId);
        userTree.setLevel(level);
        userTree.save();
    }

    @Override
    public void batchSave(List<UserTree> userTrees) {
        Db.batchSave(userTrees, 1000);
    }

    @Override
    public int listMktTeamNumByLevelFloor(Long userId, int teamLevel, int floorNum) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select sum(1) from (")
                .append(" select count(distinct tt.mkt_user_id) total from (")
                .append(" SELECT ut.mkt_user_id FROM   t_user_tree ut ")
                .append(" LEFT JOIN t_user u on ut.user_id = u.user_id ")
                .append(" where ut.sup_user_id = ? ")
                .append(" and ut.user_id != ut.sup_user_id ")
                .append(" and u.floor <= ? ")
                .append(" and u.team_level >= ? ")
                .append(" ) tt group by tt.mkt_user_id ")
                .append(" ) tt2 ");
        Integer count = Db.queryInt(sql.toString(), userId, floorNum, teamLevel);
        return null == count ? 0 : count;
    }

    @Override
    public Long sumTeamCredit(Long userId) {
        String sql = "select sum(t2.credit_num) from t_user_tree t1\n" +
                "inner join t_user_ext t2 on t1.user_id = t2.user_id \n" +
                " where sup_user_id = ? and level > 0";

        Long res = Db.queryLong(sql, userId);
        return res == null ? 0l : res;
    }

    @Override
    public List<Record> listMktUserAndCount(Long userId) {
        String sql = "select mkt_user_id,sum(t2.credit_num) credit_sum from t_user_tree t1\n" +
                "left join t_user_ext t2 on t1.user_id = t2.user_id \n" +
                " where sup_user_id = ? and mkt_user_id <> ? group by mkt_user_id";

        return Db.find(sql, userId, userId);
    }

    @Override
    public Long sumTeamCreditMin(Long userId) {
        List<Record> list = listMktUserAndCount(userId);
        // 去掉两个大区的值
        Long max1 = 0l; // 最大值
        Long max2 = 0l; // 第二大值
        Long total = 0l;
        for (Record record : list) {
            Long tmp = record.getLong("credit_sum");
            tmp = tmp == null ? 0l : tmp;
            total += tmp;
            // 大于当前最大值需要进行更新
            if (tmp > max1) {
                // 更新第二大值
                max2 = max1;
                // 更新最大值
                max1 = tmp;
            }
        }

        return total - max1 - max2;

    }

    @Override
    public Integer getTeamNum(Long userId) {
        String sql = "select count(id) as num from t_user_tree \n" +
                " where sup_user_id = ? and level <> 0 and  user_id in (select user_id from t_user where face_auth=1 )";

        Integer res = Db.queryInt(sql, userId);
        return res == null ? 0 : res;
    }

    @Override
    public Integer getTeamChildNum(Long userId) {
        String sql = "select count(id) as num from t_user_tree \n" +
                " where sup_user_id = ? and level=1 and  user_id in (select user_id from t_user where face_auth=1 )";

        Integer res = Db.queryInt(sql, userId);
        return res == null ? 0 : res;
    }

    public void cmfAgent(Long userId, Long supUserId) {
        CmfAgent cmfAgent = new CmfAgent();
        cmfAgent.setUid(userId);
        cmfAgent.setOne(supUserId);
        Long a = System.currentTimeMillis() / 1000;
        cmfAgent.setAddtime(Integer.parseInt(a.toString()));
        cmfAgent.save();
    }

    @Override
    public void deleteTree(Long userId) {
        StringBuilder sql = new StringBuilder();
        sql.append("delete from t_user_tree where sup_user_id=? ");
        Db.delete(sql.toString(), userId);
    }

    @Override
    public void addUserTree(Long userId, Long supUserId, Integer level, Date createTime) {
        UserTreeH userTreeH = new UserTreeH();
        userTreeH.setUserId(userId);
        userTreeH.setSupUserId(supUserId);
        userTreeH.setLevel(level);
        userTreeH.setCreateTime(createTime);
        userTreeH.save();
    }


    @Override
    public void batchUserTreeH(List<UserTreeH> userTreeHS) {
        Db.batchSave(userTreeHS, 100);
    }

    @Override
    public List<Record> listTeamForDate(Long userId, Date date) {
        StringBuffer sql = new StringBuffer();
        sql.append(" select DATE_FORMAT(t1.create_time,'%Y-%m-%d') as date, count(1) as num from t_user_tree t1 ")
                .append(" left join t_user t2 on t2.user_id = t1.user_id ")
                .append(" where t1.sup_user_id = ? and t1.create_time > ? and t1.level <> 0 and t2.face_auth='1' ")
                .append(" group by DATE_FORMAT(t1.create_time,'%Y-%m-%d') ");
        return Db.find(sql.toString(), userId, date);
    }

    @Override
    public Integer countTaskTeamNum(Long userId, Date date) {
        StringBuffer sql = new StringBuffer();
        sql.append(" select count(1) as num from t_user_tree t1, t_user t2  ")
                .append(" where t1.sup_user_id = ? and t1.create_time > ? and t1.level <> 0 and t2.user_id = t1.user_id and t2.face_auth='1' ");
        return Db.queryInt(sql.toString(), userId, date);
    }
}
